package com.gitee.neuray.security.wb.ribs.biz;

import com.ace.cache.api.impl.CacheRedis;
import com.alibaba.fastjson.JSONObject;
import com.gitee.neuray.security.common.util.UUIDUtils;
import com.gitee.neuray.security.wb.ribs.constants.WbApiConstants;
import com.gitee.neuray.security.wb.ribs.feign.FileFeign;
import com.gitee.neuray.security.wb.ribs.feign.StaffFeign;
import com.gitee.neuray.security.wb.ribs.util.BigDecimalUtils;
import com.gitee.neuray.security.wb.ribs.util.MoneyUtil;
import com.gitee.neuray.security.wb.ribs.util.PoiUtil;
import com.gitee.neuray.security.wb.ribs.util.wechat.Dictionary;
import com.gitee.neuray.security.wb.ribs.util.wechat.HttpUtil;
import com.gitee.neuray.security.wb.ribs.util.wechat.entity.*;
import com.gitee.neuray.security.wb.ribs.vo.*;
import org.apache.http.HttpEntity;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.entity.mime.HttpMultipartMode;
import org.apache.http.entity.mime.MultipartEntityBuilder;
import org.apache.http.entity.mime.content.FileBody;
import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 微信企业号服务层
 */
@Service
public class WechatBiz {

    private static Logger logger = Logger.getLogger(WechatBiz.class);

    @Autowired
    private CacheRedis cacheRedis;

    @Autowired
    private FileFeign fileFeign;

    @Autowired
    private ClfBiz clfBiz;

    @Autowired
    private CyfBiz cyfBiz;

    @Autowired
    private JtfBiz jtfBiz;

    @Autowired
    private QtfyBiz qtfyBiz;

    @Autowired
    private RibsBiz ribsBiz;

    @Autowired
    private StaffFeign staffFeign;

    /**
     * 上传文件至微信服务，注意，文件仅保存3天
     *
     * @return
     */
    public MediaUploadInfo mediaUpload(String accessToken,String type,HttpServletRequest request,Map staff,XSSFWorkbook wb,String userId) throws IOException{
        AccessToken xxAccessToken = getAccessToken("xx");
        String xxToken = xxAccessToken.getAccessToken();

        MediaUploadInfo mediaUploadInfo = new MediaUploadInfo();


        CloseableHttpClient clients = HttpUtil.getHttpClient();
        HttpPost requests = new HttpPost(String.format(Dictionary.MEDIA_UPLOAD_URL, accessToken, "file"));

        ByteArrayOutputStream boss=new ByteArrayOutputStream();
        wb.write(boss);
        byte[] barray=boss.toByteArray();
        InputStream is=new ByteArrayInputStream(barray);
        //todo 创建文件夹 临时文件
        String savePath = request.getServletContext().getRealPath("/temp");
        //String savePath = "D://temp";
        File tmpDir = new File(savePath);
        // 判断上传文件的保存目录是否存在
        if (!tmpDir.exists() && !tmpDir.isDirectory())
        {
            //tmpDir.createNewFile();
            logger.debug(savePath + "目录不存在，需要创建。");

            // 创建目录
            tmpDir.mkdir();
        }
        String name = staff.get("name").toString();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        //name = java.net.URLEncoder.encode(name + "报销", "UTF-8");
        File fileTemp = new File(savePath + File.separator + name + "-"+ type +"-" + df.format(new Date()) + ".xlsx");
        System.out.println("STEP9-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        FileOutputStream fileOutputStream = new FileOutputStream(fileTemp);
        //将 下载wb的inputStream 通过FileOutputStream写入临时文件
        byte[] b = new byte[1024];
        int n;
        while ((n = is.read(b)) != -1)
        {
            fileOutputStream.write(b, 0, n);
        }
        //=================================================
        //设置请求参数，把文件上传给微信端
        FileBody bin = new FileBody(fileTemp);
        HttpEntity reqEntity = MultipartEntityBuilder.create().setMode(HttpMultipartMode.RFC6532)
                .addPart("media",bin)
                .build();
        requests.addHeader("Content-Disposition",String.format("form-data;name=\"media\" filename=\""+name + "\""));
        requests.setEntity(reqEntity);

        try (CloseableHttpResponse response = clients.execute(requests)) {
            String responseContent = new BasicResponseHandler().handleResponse(response);
            WechatError error = JSONObject.parseObject(responseContent, WechatError.class);
            if (error.getErrCode() != 0) {
                throw new Exception(error.toString());
            }
            mediaUploadInfo = JSONObject.parseObject(responseContent, MediaUploadInfo.class);
            System.out.println("STEP10-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            //删除临时文件
            fileTemp.delete();
            System.out.println("STEP11-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            logger.info(mediaUploadInfo.getMediaId());
            //调用发送文件 accessToken改为消息应用发送文件消息
            SendMsgInfo sendMsgInfo = this.sendMsg(xxToken,userId,mediaUploadInfo.getMediaId(),"file",null);
            System.out.println("STEP12-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            return mediaUploadInfo;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public SendMsgInfo sendMsg(String accessToken,String userId,String mediaId,String msgType,String content){
        SendMsgInfo sendMsgInfo = new SendMsgInfo();

        //如果不存在调用微信企业号获取accesstoken接口，获取token
        CloseableHttpClient clients = HttpUtil.getHttpClient();
        HttpPost requests = new HttpPost(String.format(Dictionary.SEND_MSG_URL, accessToken));

        //发送微信消息必要参数 其中 touser、toparty、totag三者不能全为空
        JSONObject mapParam = new JSONObject();
        mapParam.put("touser",userId); //接收用户id 使用|分隔
        mapParam.put("toparty",""); //部门id 使用|分隔
        mapParam.put("totag",""); //标签id 使用|分隔
        mapParam.put("msgtype",msgType); //固定为file 其他见官网api
        mapParam.put("agentid",Dictionary.XX_AGENT_ID); //企业应用id，可在企业微信应用设置页面查看
        mapParam.put("safe",0); //是否为保密 0是
        mapParam.put("enable_duplicate_check",0); //是否开启重复消息检查 0是
        mapParam.put("duplicate_check_interval",1800); //重复消息检查间隔 1800s 最大不超过4小时
        if("file".equals(msgType)){
            JSONObject fileParam = new JSONObject();
            fileParam.put("media_id",mediaId); //微信服务文件id
            mapParam.put("file",fileParam);
        }else if("text".equals(msgType)){
            JSONObject contentParam = new JSONObject();
            contentParam.put("content",content); //微信服务文件id
            mapParam.put("text",contentParam);
        }
        requests.setEntity(new StringEntity(JSONObject.toJSONString(mapParam), "utf-8"));


        //requests.setHeader("Content-Type", contentType);

        try (CloseableHttpResponse response = clients.execute(requests)) {
            String responseContent = new BasicResponseHandler().handleResponse(response);
            WechatError error = JSONObject.parseObject(responseContent, WechatError.class);
            if (error.getErrCode() != 0) {
                throw new Exception(error.toString());
            }
            sendMsgInfo = JSONObject.parseObject(responseContent, SendMsgInfo.class);

            logger.info(sendMsgInfo.getInvaliduser());
            return sendMsgInfo;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 1 处理excel 并 发送给用户
     * @param map
     * @param type
     * @param request
     * @throws IOException
     */
    public void excelAndSend(Map map, String type, RibsVo ribsVo, Map staff, HttpServletRequest request) throws IOException{
        String accessToken = map.get("accessToken").toString();
        if(StringUtils.isEmpty(accessToken)){
           accessToken = getAccessToken("wb").getAccessToken();
        }
        String userId = map.get("wxUserId").toString();

        String bxdh = ribsVo.getBxdh();
        String bxlx = ribsVo.getBxlx();
        //todo 根据报销单号获取对应的报销类型
        //String[] idsArr = ids.split(",");

        //下载报销模板 转成Excel格式
        //Map staff = JSONObject.parseObject(staffMapStr,Map.class);
        System.out.println("STEP66-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        byte[] res = fileFeign.downloadModel(WbApiConstants.FILE_ID);
        System.out.println("STEP666-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        InputStream sbs = new ByteArrayInputStream(res);
        XSSFWorkbook wb = new XSSFWorkbook(sbs);

        //根据不同报销类型 查询对应报销详情 并将信息insert into excel

        List<ClfVo> clfVos = new ArrayList<>();
        List<JtfVo> jtfVos = new ArrayList<>();
        List<CyfVo> cyfVos = new ArrayList<>();
        List<QtVo> qtVos = new ArrayList<>();
        XSSFSheet fkdSheet = wb.getSheetAt(1);
//        XSSFSheet jtfSheet = wb.getSheetAt(2);
//        XSSFSheet cyfSheet = wb.getSheetAt(3);
//        XSSFSheet clfSheet = wb.getSheetAt(4);
        if("1".equals(bxlx)){ //差旅费
            ClfVo clfVo = new ClfVo();
            clfVo.setBxdh(bxdh);
            clfVos = clfBiz.selectList(clfVo);
            clfSheet(wb,clfVos,staff,ribsVo);
        }else if("2".equals(bxlx)){ //交通费
            JtfVo jtfVo = new JtfVo();
            jtfVo.setBxdh(bxdh);
            jtfVos = jtfBiz.selectList(jtfVo);
            jtfSheet(wb,jtfVos,staff);
        }else if("3".equals(bxlx)){ //餐饮费
            CyfVo cyfVo = new CyfVo();
            cyfVo.setBxdh(bxdh);
            cyfVos = cyfBiz.selectList(cyfVo);
            cyfSheet(wb,cyfVos,staff);
        }else if("5".equals(bxlx)){ //餐饮费
            QtVo qtVo = new QtVo();
            qtVo.setBxdh(bxdh);
            qtVos = qtfyBiz.selectList(qtVo);
            qtSheet(wb,qtVos,staff);
        }
        System.out.println("STEP7-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        //处理付款单
        ribsSheet(fkdSheet,ribsVo,staff);
        System.out.println("STEP8-------------------------------------"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));

        //将excel转为字节
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            wb.write(bos);
        } finally {
            bos.close();
        }
        byte[] bytes = bos.toByteArray();

        //todo 处理完excel上传至微信服务端
        this.mediaUpload(accessToken,type,request,staff,wb,userId);

    }

    public XSSFSheet jtfSheet(XSSFWorkbook wb,List<JtfVo> jtfVos,Map staffInfo){
        String name = staffInfo.get("name").toString();
        XSSFSheet jtfSheet = wb.getSheetAt(2);
        // BigDecimalUtils.add();
        //计算需要新增的行数
        int jtfVosSize = jtfVos.size();
        int insertRows = 0;
        if(jtfVosSize > 6){
            insertRows = jtfVosSize - 6;
        }
        //新增行
        if(insertRows != 0){
            PoiUtil.insertRow(jtfSheet,8,insertRows);
        }
        int position = 3;
        for(int i = 0;i<jtfVos.size();i++){
            jtfSheet.getRow(position).getCell(0).setCellValue((i+1)+"");
            jtfSheet.getRow(position).getCell(1).setCellValue(jtfVos.get(i).getFyrq());
            jtfSheet.getRow(position).getCell(2).setCellValue(jtfVos.get(i).getSy());
            jtfSheet.getRow(position).getCell(3).setCellValue(jtfVos.get(i).getCfd());
            jtfSheet.getRow(position).getCell(4).setCellValue(jtfVos.get(i).getMdd());
            jtfSheet.getRow(position).getCell(5).setCellValue(Double.valueOf(jtfVos.get(i).getJe()));
            jtfSheet.getRow(position).getCell(6).setCellValue(staffInfo.get("orgName").toString());
            jtfSheet.getRow(position).getCell(7).setCellValue(jtfVos.get(i).getBz());
            position ++ ;
        }
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        String je = jtfSheet.getRow(11+insertRows).getCell(2).getStringCellValue().substring(1);
        String jeC = MoneyUtil.toChinese(je);
        jtfSheet.getRow(10+insertRows).getCell(2).setCellValue(jeC);
        jtfSheet.getRow(13+insertRows).createCell(7).setCellValue(name);
        return jtfSheet;

    }

    /**
     * 差旅费
     * @param wb
     * @param clfVos
     * @param staffInfo
     * @return
     */
    public XSSFSheet clfSheet(XSSFWorkbook wb,List<ClfVo> clfVos,Map staffInfo,RibsVo ribsVo){
        String name = staffInfo.get("name").toString();
        String orgName = staffInfo.get("orgName").toString();
        XSSFSheet clfSheet = wb.getSheetAt(4);
        //设置出差人、项目组（组织机构）、出差事由(删除)
        clfSheet.getRow(4).getCell(1).setCellValue(name);
        clfSheet.getRow(4).getCell(5).setCellValue(orgName);

        // BigDecimalUtils.add();
        //计算需要新增的行数
        int clfVosSize = clfVos.size();
        int insertRows = 0;
        /*if(clfVosSize*2 > 6){
            insertRows = clfVosSize*2 - 6;
        }*/
        if(clfVosSize > 6){
            insertRows = clfVosSize - 6;
        }
        //新增行
        if(insertRows != 0){
            PoiUtil.insertRow(clfSheet,13,insertRows);
        }
        int position = 7;
        for(int i = 0;i<clfVos.size();i++){
            //去程
            clfSheet.getRow(position).getCell(0).setCellValue(clfVos.get(i).getGCfsj());
            clfSheet.getRow(position).getCell(1).setCellValue(clfVos.get(i).getGCfd());
            clfSheet.getRow(position).getCell(2).setCellValue(clfVos.get(i).getGMdd());
            /** 去_出行方式 codelist name = CXFS 1、火车及动车 2、火车及动车一等座 3、飞机 4其他 */
            if("1".equals(clfVos.get(i).getGCxfs()) || "2".equals(clfVos.get(i).getGCxfs())){
                clfSheet.getRow(position).getCell(3).setCellValue(Double.valueOf(clfVos.get(i).getGJtf() == null || "".equals(clfVos.get(i).getGJtf()) ? "0" : clfVos.get(i).getGJtf()));
            }else if("3".equals(clfVos.get(i).getGCxfs())){
                clfSheet.getRow(position).getCell(4).setCellValue(Double.valueOf(clfVos.get(i).getGJtf() == null || "".equals(clfVos.get(i).getGJtf()) ? "0" : clfVos.get(i).getGJtf()));
            }else if("4".equals(clfVos.get(i).getGCxfs())){
                clfSheet.getRow(position).getCell(5).setCellValue(Double.valueOf(clfVos.get(i).getGJtf() == null || "".equals(clfVos.get(i).getGJtf()) ? "0" : clfVos.get(i).getGJtf()));
            }

            clfSheet.getRow(position).getCell(6).setCellValue(Double.valueOf(clfVos.get(i).getCcbz() == null || "".equals(clfVos.get(i).getCcbz()) ? "0" : clfVos.get(i).getCcbz()));
            clfSheet.getRow(position).getCell(7).setCellValue(clfVos.get(i).getZsts());
            clfSheet.getRow(position).getCell(8).setCellValue(clfVos.get(i).getZsrs());
            clfSheet.getRow(position).getCell(9).setCellValue(Double.valueOf(clfVos.get(i).getZsje() == null || "".equals(clfVos.get(i).getZsje()) ? "0" : clfVos.get(i).getZsje()));
            clfSheet.getRow(position).getCell(10).setCellValue(Double.valueOf(clfVos.get(i).getZsbz() == null || "".equals(clfVos.get(i).getZsbz()) ? "0" : clfVos.get(i).getZsbz()));

            String qtfy = "";
            String qtfysm = "";
            String bz = "";
            if(clfVos.get(i).getQtfy() != null){
                qtfy = clfVos.get(i).getQtfy();
            }
            if(clfVos.get(i).getQtfysm() != null){
                qtfysm = clfVos.get(i).getQtfysm();
            }
            if(clfVos.get(i).getBz() != null){
                bz = clfVos.get(i).getBz();
            }

            clfSheet.getRow(position).getCell(11).setCellValue(qtfy+ "(" + qtfysm + ")。" +bz);

            /*position ++;
            //回程
            clfSheet.getRow(position).getCell(0).setCellValue(clfVos.get(i).getBCfsj());
            clfSheet.getRow(position).getCell(1).setCellValue(clfVos.get(i).getBCfd());
            clfSheet.getRow(position).getCell(2).setCellValue(clfVos.get(i).getBMdd());
            *//** 去_出行方式 codelist name = CXFS 1、火车及动车 2、飞机 3其他 *//*
            if("1".equals(clfVos.get(i).getBCxfs())||"2".equals(clfVos.get(i).getBCxfs())){
                clfSheet.getRow(position).getCell(3).setCellValue(Double.valueOf(clfVos.get(i).getBJtf()));
            }else if("3".equals(clfVos.get(i).getBCxfs())){
                clfSheet.getRow(position).getCell(4).setCellValue(Double.valueOf(clfVos.get(i).getBJtf()));
            }else if("4".equals(clfVos.get(i).getBCxfs())){
                clfSheet.getRow(position).getCell(5).setCellValue(Double.valueOf(clfVos.get(i).getBJtf()));
            }
            clfSheet.getRow(position).getCell(11).setCellValue(clfVos.get(i).getBz());
*/
            position ++ ;
        }
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        String je = ribsVo.getFyhj();
        clfSheet.getRow(16+insertRows).getCell(1).setCellValue(Double.valueOf(je));
        String jeC = MoneyUtil.toChinese(je);
        clfSheet.getRow(15+insertRows).getCell(1).setCellValue(jeC);
        clfSheet.getRow(14+insertRows).getCell(1).setCellValue(je);
        clfSheet.getRow(20+insertRows).createCell(11).setCellValue(name);

        return clfSheet;

    }
    public XSSFSheet cyfSheet(XSSFWorkbook wb,List<CyfVo> cyfVos,Map staffInfo){
        String name = staffInfo.get("name").toString();
        XSSFSheet cyfSheet = wb.getSheetAt(3);
        // BigDecimalUtils.add();
        //计算需要新增的行数
        int cyfVosSize = cyfVos.size();
        int insertRows = 0;
        if(cyfVosSize > 6){
            insertRows = cyfVosSize - 6;
        }
        //新增行
        if(insertRows != 0){
            PoiUtil.insertRow(cyfSheet,7,insertRows);
        }
        int position = 3;
        for(int i = 0;i<cyfVos.size();i++){
            cyfSheet.getRow(position).getCell(0).setCellValue((i+1)+"");
            cyfSheet.getRow(position).getCell(1).setCellValue(cyfVos.get(i).getZdsj());
            cyfSheet.getRow(position).getCell(2).setCellValue(cyfVos.get(i).getZdsy());
            /** 客户类别 1、客户 2、供应商 3、其他 */
            String khlb = cyfVos.get(i).getKhlb();
            String khlbmc = "";
            if("1".equals(khlb)){
                khlbmc = "客户";
            }else if("2".equals(khlb)){
                khlbmc = "供应商";
            }else if("3".equals(khlb)){
                khlbmc = "其他";
            }
            cyfSheet.getRow(position).getCell(3).setCellValue(khlbmc);
            cyfSheet.getRow(position).getCell(4).setCellValue(cyfVos.get(i).getKhmc());
            cyfSheet.getRow(position).getCell(5).setCellValue(cyfVos.get(i).getZddd());
            cyfSheet.getRow(position).getCell(6).setCellValue(cyfVos.get(i).getStry());
            cyfSheet.getRow(position).getCell(7).setCellValue(cyfVos.get(i).getJe());
            cyfSheet.getRow(position).getCell(8).setCellValue(staffInfo.get("orgName").toString());
            position ++ ;
        }
        /*XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        String je = cyfSheet.getRow(11+insertRows).getCell(2).getStringCellValue().substring(1);
        String jeC = MoneyUtil.toChinese(je);
        cyfSheet.getRow(10+insertRows).getCell(2).setCellValue(jeC);*/
        cyfSheet.getRow(9+insertRows).getCell(8).setCellValue(name);
        return cyfSheet;

    }

    /**
     * 其他费用
     * @param wb
     * @param qtVos
     * @param staffInfo
     * @return
     */
    public XSSFSheet qtSheet(XSSFWorkbook wb, List<QtVo> qtVos, Map staffInfo){
        String name = staffInfo.get("name").toString();
        XSSFSheet qtSheet = wb.getSheetAt(5);
        // BigDecimalUtils.add();
        //计算需要新增的行数
        int qtVosSize = qtVos.size();
        int insertRows = 0;
        if(qtVosSize > 6){
            insertRows = qtVosSize - 6;
        }
        //新增行
        if(insertRows != 0){
            PoiUtil.insertRow(qtSheet,7,insertRows);
        }
        int position = 3;
        for(int i = 0;i<qtVos.size();i++){
            qtSheet.getRow(position).getCell(0).setCellValue((i+1)+"");
            qtSheet.getRow(position).getCell(1).setCellValue(qtVos.get(i).getFssj());
            qtSheet.getRow(position).getCell(3).setCellValue(qtVos.get(i).getSy());
            qtSheet.getRow(position).getCell(5).setCellValue(qtVos.get(i).getJe());
            qtSheet.getRow(position).getCell(6).setCellValue(qtVos.get(i).getBz());
            position ++ ;
        }
        qtSheet.getRow(9+insertRows).getCell(7).setCellValue(name);
        return qtSheet;

    }

    public XSSFSheet ribsSheet(XSSFSheet ribsSheet,RibsVo ribsVo,Map staffInfo){
        String name = staffInfo.get("name").toString();
        //取第三行，报销单号
        ribsSheet.getRow(2).getCell(7).setCellValue(ribsVo.getBxdh());
        //取第四行，签发日期，结算方式
        ribsSheet.getRow(3).getCell(1).setCellValue(ribsVo.getQfrq());
        String jsfs = "";
        if("1".equals(ribsVo.getJsfs())){
            jsfs = "电汇";
        }else if("2".equals(ribsVo.getJsfs())){
            jsfs = "现金";
        }else if("3".equals(ribsVo.getJsfs())){
            jsfs = "其他";
        }
        ribsSheet.getRow(3).getCell(9).setCellValue(jsfs);
        //取第五行，收款单位，项目组(现在是组织机构)
        ribsSheet.getRow(4).getCell(1).setCellValue(staffInfo.get("name").toString());
        ribsSheet.getRow(4).getCell(8).setCellValue(staffInfo.get("orgName").toString());
        //取第六行，收款账号
        ribsSheet.getRow(5).getCell(1).setCellValue(ribsVo.getYhkh());
        //取第七行，收款账户
        ribsSheet.getRow(6).getCell(1).setCellValue(ribsVo.getYhmc());
        //取第九行，结算款，报销事由
            //计算款
        String jsk = ribsVo.getFyhj();
        int jskFL = 0;
        int jskSL = 0;
        String jskF = "";
        String jskS = "";
        String[] jskArr = null;
        int positionF = 9; //元 的位置
        if(jsk.contains(".")){
            jskArr = jsk.split("\\.");
            jskFL = jskArr[0].length(); //小数点前数字长度
            jskF = jskArr[0]; //小数点前数字
            jskSL = jskArr[1].length(); //小数点后数字长度
            jskS = jskArr[1]; //小数点后数字
        }else if(!"0".equals(jsk)){
            jskFL = jsk.length();
            jskF = jsk;
        }
            //小数点前赋值
        for(int i=jskFL-1;i>=0;i--){
            String jskCharF = String.valueOf(jskF.charAt(i));
            ribsSheet.getRow(8).getCell(positionF).setCellValue(jskCharF);
            positionF --;
        }
        ribsSheet.getRow(8).getCell(positionF).setCellValue("￥");
        ribsSheet.getRow(7).getCell(1).setCellValue(MoneyUtil.toChinese(jsk));
            //小数点后赋值
        int positionS = 10; //角 的位置
        for(int j=0;j<jskSL;j++){
            if(j > 1){
                break;
            }
            String jskCharS = String.valueOf(jskS.charAt(j));
            ribsSheet.getRow(8).getCell(positionS).setCellValue(jskCharS);
            positionS ++;
        }
            //如果小数点后面只有一位，分的位置设为0 如果小数点后面什么也没有，角分的位置设为0
        if(jskSL == 1){
            ribsSheet.getRow(8).getCell(11).setCellValue("0");
        }else if(jskSL == 0){
            ribsSheet.getRow(8).getCell(11).setCellValue("0");
            ribsSheet.getRow(8).getCell(10).setCellValue("0");
        }
            //报销事由
        ribsSheet.getRow(8).getCell(1).setCellValue(ribsVo.getBxsy());
        //取第10行 备注
        ribsSheet.getRow(9).getCell(1).setCellValue(ribsVo.getBz());
        ribsSheet.getRow(11).getCell(11).setCellValue(name);
        return ribsSheet;

    }

    /**
     * 获取微信企业号accessToken
     *
     * @return
     */
    public AccessToken getAccessToken(String type) {

        AccessToken accessToken = new AccessToken();

        //判断缓存中是否存在accessToken（有效期60分钟）
        //将获取的token存入缓存，设置有效期60分钟
        String tokenCache = "";
        if("type".equals("wb")){
            tokenCache = cacheRedis.get("wbAccessToken");
        }else{
            tokenCache = cacheRedis.get("xxAccessToken");
        }
        //如果存在，则直接返回accessToken
        if (tokenCache!= null &&!tokenCache.isEmpty()) {
            return JSONObject.parseObject(tokenCache,AccessToken.class);
        }

        //如果不存在调用微信企业号获取accesstoken接口，获取token
        CloseableHttpClient client = HttpUtil.getHttpClient();
        //todo 不同应用使用不同的应用密钥，根据自定义参数state获取
        String corpSecret = "";
        if("type".equals("wb")){
            corpSecret = Dictionary.CORPSECRET;
        }else{
            corpSecret = Dictionary.XX_CORPSECRET;
        }
        HttpGet request = new HttpGet(String.format(Dictionary.GET_ACCESS_TOKEN_URL, Dictionary.CORPID,
                corpSecret));
        try (CloseableHttpResponse response = client.execute(request)) {
            String responseContent = new BasicResponseHandler().handleResponse(response);
            WechatError error = JSONObject.parseObject(responseContent, WechatError.class);
            if (error.getErrCode() != 0) {
                throw new Exception(error.toString());
            }
            accessToken = JSONObject.parseObject(responseContent, AccessToken.class);
            accessToken.setTime(System.currentTimeMillis());

            //将获取的token存入缓存，设置有效期60分钟
            if("type".equals("wb")){
                cacheRedis.set("wbAccessToken",JSONObject.toJSONString(accessToken),Dictionary.ACCESS_TOKEN_TIMEOUT_MIN);
            }else{
                cacheRedis.set("xxAccessToken",JSONObject.toJSONString(accessToken),Dictionary.ACCESS_TOKEN_TIMEOUT_MIN);
            }

            logger.info(accessToken.getAccessToken());
            return accessToken;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) throws Exception {
        System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        /*String a = "￥4";
        String jskArr = a.substring(1);
        jskArr.replaceAll(",", "");
        System.out.println(jskArr);*/
        /*int jskFL = jskArr[0].length();
        int jskSL = jskArr[1].length();
        for(int i=0;i<jskSL;i++){
            System.out.println(i);
            System.out.println(jskArr[1].charAt(i));
        }*/

        /*File inputFile = new File("d:\\保险单模板.xlsx");
        InputStream is = new FileInputStream(inputFile);
        Workbook wb = new XSSFWorkbook(is);
        Sheet sheet = wb.getSheetAt(1);

        // 直接调用，我知道合并单元格的位置：
        System.out.println(sheet.getRow(3).getCell(1).getDateCellValue());

        System.out.println(sheet.getRow(4).getCell(8).getStringCellValue());

        wb.close();
        is.close();*/
    }
}
